<?php 
define('KONSTANTA_TUNJANGAN_KJK'	,	2);
define('KONSTANTA_TUNJANGAN_KHK'	,	1.5);	
define("KONSTANTA_POTONGAN_ABSEN"	,	0.75);
function tutup_bulan($schedule){
	$periode_id =get_periode_absen();
	compress_sql_waktu_kerja($periode_id);
	//update realisasi
	$query = "TRUNCATE TABLE a_realisasi_calc";
	$result = my_query($query);		
	//update_realisasi() ;
	
	// MOVE TO LOG
	$query = "INSERT INTO a_realisasi_log
		SELECT *,".$periode_id.",0  
		FROM a_realisasi_calc" ;	
	my_query($query); 	

	//a_absen_payroll
	update_a_absen_payroll() ;
	
	//PROSES KJK
	proses_transfer_kjk();
	
	//PROSES KHK 
	proses_transfer_khk();
	
	//proses_ hitung absen
	proses_hitung_absen( $periode_id );
	
	//copy temp_tmn_hari_new to temp_tmn_hari THEN  TRUNCATE temp_tmn_hari_new
	$query_temp_tmn_hari_new = "INSERT INTO temp_tmn_hari (tgl_index ,	hari_libur,	karyawan_id,	nik,	status_absen)
			   SELECT tgl_index	,hari_libur,	karyawan_id,	nik	,status_absen FROM temp_tmn_hari_new";
	my_query($query_temp_tmn_hari_new); 

	$query = "TRUNCATE TABLE temp_tmn_hari_new";
	my_query($query);
	
	$query_overtime = "
	INSERT INTO temp_tmn_overtime (karyawan_id,	nik	,tanggal,	daytype	,groupday,	hour,	hourpay	,out_of_count,	nominal)
	SELECT karyawan_id	,nik	,tanggal	,daytype	,groupday	,hour	,hourpay,	out_of_count	,nominal FROM  temp_tmn_overtime_new";
	my_query($query_overtime); 
	$query = "TRUNCATE TABLE temp_tmn_overtime_new";
	my_query($query);

	//TRUNCATE SCHEDULE
	$query = "TRUNCATE TABLE a_temp_jadwal_karyawan";
	my_query($query);
	
	$query = "TRUNCATE TABLE a_realisasi";
	$result = my_query($query);	
	$query = "TRUNCATE TABLE a_realisasi_calc";
	$result = my_query($query);		

	$periode_id = update_periode_id();
	
	//if($schedule=='true' ){
		kalendar_shift_proses_update($periode_id  );
	//}	
	return true ;
}


function hitung_tidak_finger($karyawan_id){
	$query = "SELECT COUNT(*) AS ttl FROM temp_tmn_hari_new WHERE karyawan_id = {$karyawan_id} ";
	$query .= " AND status_absen = 'TF' ";  
	$result = my_query($query);
	$row = my_fetch_array($result);
	return $row['ttl'];
}

function  proses_hitung_absen( $periode_id ){
	$query = "SELECT karyawan_id, SUM( margin_calc ) AS a_margin
	FROM temp_proses_finger
	GROUP BY karyawan_id";
	$result = my_query($query);
	$i=0;
	while( $row = my_fetch_array($result) ){
		$karyawan = loaddata_karyawan($row['karyawan_id']);
		$hari_no_finger = hitung_tidak_finger($row['karyawan_id']);
		$waktu_no_finger = 60 * 8 * $hari_no_finger;
		$menit = 0 - $waktu_no_finger;
		$nominal = KONSTANTA_POTONGAN_ABSEN *  $row['a_margin']  * $karyawan['gapok_km'] / (60 * 173);
		if($nominal == 0 )continue; 
		$datas = array( 
			'nik'			=> my_type_data_str($karyawan['nik']) , 
			'karyawan_id'		=> my_type_data_str($row['karyawan_id']) , 
			'nmenit'		=> my_type_data_str($row['a_margin']) ,
			'no_finger'		=> my_type_data_str($waktu_no_finger) ,
			'nominal'		=> my_type_data_str( (-1) * $nominal ) ,
		);
		my_insert_record('temp_tmn_absensi' , $datas);
		$i++;
	}

	
	$query_export = " 
	INSERT INTO tmn_proses_finger ( 
		periode_id ,	karyawan_id	,
		proses_date	, jam_in	, jam_out ,
		correct_in , correct_out , margin_in , margin_out ,
		margin_calc	, datetime_updated )
	SELECT  {$periode_id} ,	karyawan_id	,
		proses_date,	jam_in	,jam_out	,
		correct_in ,
		correct_out , margin_in	,
		margin_out , margin_calc	,
		datetime_updated FROM temp_proses_finger ";
	my_query($query_export);

	my_query("TRUNCATE TABLE temp_proses_finger");
	return $i;
}


function proses_transfer_khk(){
	$query = "SELECT nik , karyawan_id,	nhari FROM temp_tmn_khk_new";
	$result = my_query($query);
	$i=0;
	while( $row = my_fetch_array($result) ){
		$karyawan = loaddata_karyawan($row['karyawan_id']); 
		$nominal = $row['nhari'] * $karyawan['gapok_km'] / 173;
		$datas = array(
			'nik'			=> my_type_data_str($karyawan['nik']) , 
			'karyawan_id'	=> my_type_data_str($row['karyawan_id']) , 
			'nhari'			=> my_type_data_str($row['nhari']) ,
			'nominal'		=> my_type_data_str($nominal) ,
		);
		my_insert_record('temp_tmn_khk' , $datas);
		$i++;
	}
	my_query("TRUNCATE TABLE temp_tmn_khk_new");
	return $i;
}

function proses_transfer_kjk(){
	$query = "SELECT nik	,karyawan_id,	njam FROM temp_tmn_kjk_new";
	$result = my_query($query);
	$i=0;
	while( $row = my_fetch_array($result) ){
		$karyawan = loaddata_karyawan($row['karyawan_id']); 
		$nominal =  KONSTANTA_TUNJANGAN_KJK * $row['njam'] * $karyawan['gapok_km'] / 173;
		$datas = array(
			'nik'			=> my_type_data_str($karyawan['nik']) , 
			'karyawan_id'	=> my_type_data_str($row['karyawan_id']) , 
			'njam'			=> my_type_data_str($row['njam']) ,
			'nominal'		=> my_type_data_str($nominal) ,
		);
		my_insert_record('temp_tmn_kjk' , $datas);
		$i++; 
	}
	my_query("TRUNCATE TABLE temp_tmn_kjk_new");
	return $i;
}


function update_realisasi(){
	$periode_id =get_periode_absen();
	$periode = my_get_data_by_id('pr_periode','periode_id',$periode_id);
	$bulan = (int) date('m' , strtotime($periode['tanggal']));
	$tahun = $periode['tahun'] ;
	$end=$periode['tahun'].'-'. sprintf( '%02d',$bulan) .'-10' ;
	if($bulan =='1' ){
		$startdate = ($tahun -1).'-12-11';
		$middledata = ($tahun -1).'-12-25';
		$middledata2 = ($tahun -1).'-12-26';
		$enddate = $tahun.'-'.sprintf( '%02d',$bulan) .'-10'; //hasilnya 10
	}else{
		$startdate = $tahun.'-'.sprintf( '%02d',$bulan-1) .'-11';
		$middledata =  $tahun .'-'.sprintf( '%02d',$bulan-1) .'-25';
		$middledata2 =  $tahun .'-'.sprintf( '%02d',$bulan-1) .'-26';
		$enddate =$tahun.'-'.sprintf( '%02d',$bulan) .'-10'; //hasilnya 10
	}
	$query = "SELECT max(realisasi_id) as max_no FROM a_realisasi";
		$res = my_query($query);
		$atemps = my_fetch_array($res);
		$next_id= (int) $atemps['max_no'] ;	
	$query = "
			SELECT a.karyawan_id FROM karyawan a 
			INNER JOIN biodata b 
			ON a.biodata_id = b.biodata_id
			WHERE a.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable)	
			AND a.karyawan_id NOT IN (select karyawan_id FROM pemberhentian  WHERE TMT <= '{$end}') 	
			ORDER BY a.karyawan_id
		";
	$result = my_query($query); 	
	while($ey = my_fetch_array($result )){	
		$karyawan_id=$ey['karyawan_id'] ;
		$karyawan = loaddata_karyawan($karyawan_id , false);
		$tanggals = list_kalender($startdate , $enddate  );
		$tanggals = array_unique($tanggals) ;
		$waktu_kerja_id= (int) isset( $karyawan['waktu_kerja_id'] )? $karyawan['waktu_kerja_id'] : 0 ;  
		foreach($tanggals as $tanggal){
			list($yyyy,$mm,$dd) = explode("-" , $tanggal);
			$query = "SELECT * FROM a_realisasi 
				WHERE karyawan_id = {$karyawan_id}
				AND field_tgl='tgl_{$dd}' ";
			$res = my_query($query);
			$a_reals = my_fetch_array($res);
			$realisasi_id=isset($a_reals['realisasi_id']) ? $a_reals['realisasi_id'] : 0 ;
			$get_reals=get_realisasi($tanggal , 
				$karyawan_id, 
				$waktu_kerja_id,
				$karyawan['kelompok_shift_id'],
				$karyawan['kelompok'],
				$realisasi_id) ;
			if($realisasi_id==0){
				$next_id++;
				$realisasi_id= $next_id ;
			}
			$datas = array(
				'realisasi_id'=>my_type_data_int($realisasi_id), 	
				'karyawan_id'=>my_type_data_int($karyawan_id), 	
				'field_tgl' =>my_type_data_str('tgl_'. date('d', strtotime($tanggal)) ),	
				'jam_datang' =>my_type_data_str($get_reals['jam_datang']),	
				'jam_pulang'=>my_type_data_str($get_reals['jam_pulang']),
				'sc_jadwal_kerja_kelompok_id'=>my_type_data_int($get_reals['sc_jadwal_kerja_kelompok_id']), 	
				'flag_masuk'=>my_type_data_str($get_reals['flag_masuk']),
				'flag_pulang'=>my_type_data_str($get_reals['flag_pulang']),
				'gl_status'=>my_type_data_str($get_reals['gl_status']),
				'gl_time'=>my_type_data_str($get_reals['gl_time']),
				'sc_time'=>my_type_data_str($get_reals['sc_time']),
				'sc_status'=>my_type_data_str($get_reals['sc_status']),
				'status_absen_id'=>my_type_data_int($get_reals['status_absen_id']), 	
				'pc' =>my_type_data_str($get_reals['pc']),	
			);		
			my_insert_record('a_realisasi_calc',$datas);	
		}
	}
}	


function get_realisasi($tanggal , $karyawan_id, $waktu_kerja_id, $kelompok_shift_id, $kelompok, $realisasi_id ){

	$datas = array();
// global	
	if($waktu_kerja_id>3 ){
		$kelompok=get_kelompok_shift_jadwal($tanggal, $kelompok_shift_id );
		$datas['gl_status'] =$kelompok;
	}else{
		if(get_hari_libur($tanggal)){	
			$datas['gl_status']= 'X';
		}else{
			if($waktu_kerja_id==1 ){
				$datas['gl_status']= 'N';
			}else{
				$datas['gl_status']= 'F';
			}	
		}
	}
	if($datas['gl_status']=="X"){
		$datas['gl_time'] = '';
	}else{
		$hari_id=get_hari_id_by_date($tanggal);
		$query = "SELECT jam_masuk,jam_pulang FROM a_jadwal_kerja 
				WHERE waktu_kerja_id = {$waktu_kerja_id} 
				AND kelompok='".$kelompok."' 
				AND hari_id={$hari_id}";
		$result = my_query($query);
		$row = my_fetch_array($result);	
		
		if( $row ){
			if($row['jam_masuk']=='00:00:00' AND $row['jam_pulang']=='00:00:00'){			
				$datas['gl_status'] = 'X';
			}
			$t  = date('H:i',strtotime($row['jam_masuk'])).' - '.date('H:i',strtotime($row['jam_pulang'])); 
		}else{
			$t ='';
			$datas['gl_status'] = ' ' ;
		}	
		$datas['gl_time'] = $t ;
	}	
// schedule
	list($yyyy,$mm,$dd) = explode("-" , $tanggal);	
	$ch = "SELECT * FROM a_realisasi 
		WHERE karyawan_id = {$karyawan_id} 
			AND field_tgl='tgl_{$dd}' 
			ORDER BY realisasi_id 
			DESC LIMIT 1";
	$chr = my_query($ch);
	$a_reals = my_fetch_array($chr);
	
	$sc_jadwal_kerja_kelompok_id=(int) $a_reals['sc_jadwal_kerja_kelompok_id'];
	$sc_status_absen_id=(int) $a_reals['sc_status_absen_id'];
	$sc_kelompok='0';
	if($sc_jadwal_kerja_kelompok_id==0){
		if($datas['gl_status']=="X"){
			$sc_jadwal_kerja_kelompok_id=21;		
		}else{	
			if($waktu_kerja_id>3){ //kelompoknya A,B.. harus dikonversi ke 1,2,3
				$kelompok=get_kalendar_shift_by_tanggal($tanggal, $kelompok_shift_id );		
			}		
			$query = "SELECT * FROM a_jadwal_kerja_kelompok 
				WHERE waktu_kerja_id = {$waktu_kerja_id}
				AND kelompok='{$kelompok}' ";
			$res = my_query($query);
			$row = my_fetch_array($res);
			$sc_jadwal_kerja_kelompok_id=(int) $row['jadwal_kerja_kelompok_id'];		
		}
	}

	$datas['sc_time']=''; //init
	if($sc_jadwal_kerja_kelompok_id>0 AND $sc_jadwal_kerja_kelompok_id<21){
		$jadwal_kerja_kelompoks = my_get_data_by_id('a_jadwal_kerja_kelompok','jadwal_kerja_kelompok_id',$sc_jadwal_kerja_kelompok_id);
		$hari_id=get_hari_id_by_date($tanggal);
		$query = "SELECT jam_masuk,jam_pulang FROM a_jadwal_kerja 
				WHERE waktu_kerja_id =".$jadwal_kerja_kelompoks['waktu_kerja_id']."  
				AND kelompok='".$jadwal_kerja_kelompoks['kelompok']."' 
				AND hari_id={$hari_id}";
		$jams = my_fetch_array(my_query($query));	
			$sc_jam_datang=$jams['jam_masuk'] ;
			$sc_jam_datang=(trim($sc_jam_datang)<>'') ? date('H:i',strtotime($sc_jam_datang)) :'';
			$sc_jam_pulang=$jams['jam_pulang'] ;
			$sc_jam_pulang=(trim($sc_jam_pulang)<>'') ? date('H:i',strtotime($sc_jam_pulang)) :'';
			$datas['sc_time']=$sc_jam_datang.'-'.$sc_jam_pulang ;				
	}
	//finger_diabaikan
	/*
	....
	*/
	$statuss=get_status_absen($karyawan_id , $tanggal );
	
	$s='' ;
	foreach($statuss as $status){
		$s.=$status.', ' ;
	}
	$s=substr($s,0,strlen($s)-2) ;	
	$datas['sc_status']=$s ;
	
	if($realisasi_id==0){
		$datas['flag_masuk']='T' ;
		$datas['flag_pulang']='T' ;
		 //NANTI IKUTIN GLOBAL
		$datas['jam_datang']='';
		$datas['jam_pulang']='';
	}else{
		$a_reals=my_get_data_by_id('a_realisasi','realisasi_id',$realisasi_id) ;
		$datas['flag_masuk']=$a_reals['flag_masuk'] ;
		$datas['flag_pulang']=$a_reals['flag_pulang'] ;
		$jam_datang=$a_reals['jam_datang'] ;
		if($jam_datang=='00:00:00'){
			$jam_datang='';
		}	
		$datas['jam_datang']=(trim($jam_datang)<>'') ? date('H:i',strtotime($jam_datang)) :'';
		
		$jam_pulang=$a_reals['jam_pulang'] ;
		if($jam_pulang=='00:00:00'){
			$jam_pulang='';
		}	
		$datas['jam_pulang']=(trim($jam_pulang)<>'') ? date('H:i',strtotime($jam_pulang)) :'';
	}
	$datas['sc_jadwal_kerja_kelompok_id']=$sc_jadwal_kerja_kelompok_id ;
// realisasi
	$status_absen_id=get_status_absen_id($karyawan_id , $tanggal );
	if($sc_jadwal_kerja_kelompok_id==21){	
		if($status_absen_id<>15){  //BT
			$status_absen_id=2;
		}		
		}else{
		$query = "SELECT b.label,a.kelompok,a.waktu_kerja_id FROM a_jadwal_kerja_kelompok a 
			INNER JOIN a_waktu_kerja b ON a.waktu_kerja_id=b.waktu_kerja_id 
			WHERE a.jadwal_kerja_kelompok_id=".$sc_jadwal_kerja_kelompok_id." 
			ORDER BY a.jadwal_kerja_kelompok_id ";
		$jadwal_kerjas = my_fetch_array(my_query($query));	
		$waktu_kerja_id=$jadwal_kerjas['waktu_kerja_id'] ;
		$kelompok=$jadwal_kerjas['kelompok'] ;
	}	
	if(	$status_absen_id==0){
		if($waktu_kerja_id > 3){  //shift
			if($kelompok=='X'){
				$status_absen_id=2 ; //X
			}else{
				$status_absen_id=(int) $kelompok+2 ; //1,2,3				
			}
		}else{ //non shift
			if(get_hari_libur($tanggal)){	
				$status_absen_id=2 ; //X
			}else{
				$status_absen_id=1 ; //N
			}	
		}
		if($status_absen_id<>2 
			AND trim($datas['jam_datang'])=='' 
			AND trim($datas['jam_pulang'])==''
			AND !($datas['flag_masuk']=='Y' OR $datas['flag_pulang']=='Y')
			){
			$status_absen_id=16 ; //A
		}		
	}
	
	
	$telat='' ;
	$statuss=my_get_data_by_id('a_status_absen','status_absen_id',$status_absen_id) ;
	if(	$statuss['hari_kerja']=="Y"){
		if( trim($datas['jam_datang'])=='' AND $datas['flag_masuk']<>'Y'  ){
			$status_absen_id=16 ;
		}elseif( trim($datas['jam_pulang'])=='' AND $datas['flag_pulang']<>'Y'  ){
			$status_absen_id=16 ;
		}else{	
			$jam_sc1=(trim($sc_jam_datang)=='' ? 0 : strtotime($sc_jam_datang.':00'));
			$jam_sc2=(trim($sc_jam_pulang)=='' ? 0 : strtotime($sc_jam_pulang.':00'));
			$jam_1=(trim($jam_datang)=='' ? 0 : strtotime($jam_datang.':00'));
			$jam_2=(trim($jam_pulang)=='' ? 0 : strtotime($jam_pulang.':00'));
			
			if($jam_sc1 > $jam_sc2){ //lintas tanggal
				
			}else{
				$jam_menit=0;
				if( $jam_1>$jam_sc1 AND $datas['flag_masuk']<>'Y'){
						$jam_menit=$jam_1-$jam_sc1;
				}
				if( $jam_sc2>$jam_2 AND $datas['flag_pulang']<>'Y'){
						$jam_menit+=($jam_sc2-$jam_2);
				}	
				if($jam_menit>0){
					$telat=gmdate("H:i",$jam_menit );
				}	
			}
		}	

	}
	$datas['sc_time']=$sc_jam_datang.'-'.$sc_jam_pulang ;				
	$datas['status_absen_id']=$status_absen_id ;
	$datas['pc']=$telat;	
	return $datas;	
}

function get_status_absen_id($karyawan_id , $date  ){

	//cuti
	$query="SELECT kelompok_cuti_id FROM surat_cuti 
		WHERE tanggal_mulai <='{$date}' 
		AND tanggal_selesai >='{$date}' 
		AND karyawan_id ={$karyawan_id}";	
	$result = my_query($query);
	if( my_num_rows($result) > 0 ){
		$statuss=my_fetch_array(my_query($query));	
		return $statuss['kelompok_cuti_id'];
	}
	
	//surat ijin
	$query="SELECT status_absen_id FROM a_surat_ijin 
		WHERE full_day='Y' 
		AND tanggal ='{$date}' 
		AND karyawan_id ={$karyawan_id}";	
	$result = my_query($query);
	if( my_num_rows($result) > 0 ){
		$statuss=my_fetch_array(my_query($query));	
		return $statuss['status_absen_id'];
	}	

	//spd
	$query="SELECT * FROM spd
		WHERE '".$date."' BETWEEN tanggal_mulai AND tanggal_selesai 
		AND tipe_karyawan='Karyawan' 
		AND karyawan_id ={$karyawan_id}
	";
	$result = my_query($query);
	if( my_num_rows($result) > 0 ){
		return 15 ; //sementara hardcore dulu
	}	

	//tukar shift -> $jadwal_kerja_kelompok_id
	$query="SELECT * FROM a_tukar_shift 
		WHERE tanggal  ='{$date}' 
		AND (karyawan_id ={$karyawan_id} OR tukar_karyawan_id ={$karyawan_id})
	";
	$result = my_query($query);
	if( my_num_rows($result) > 0 ){
		$tukar_shifts=my_fetch_array(my_query($query));	
		$tukar_shift_id=$tukar_shifts['tukar_shift_id'] ;
		$query="SELECT pindah_jadwal_kerja_kelompok_id FROM a_tukar_shift_detail 
			WHERE tukar_shift_id={$tukar_shift_id} 
			AND karyawan_id ={$karyawan_id}
		";
		$tukar_shift_ds=my_fetch_array(my_query($query));
		$jadwal_kerja_kelompok_id=$tukar_shift_ds['pindah_jadwal_kerja_kelompok_id'];
		
		// convert dari jadwal_kerja_kelompok_id menjadi status_absen_id
		$query = "SELECT a.kelompok,a.waktu_kerja_id FROM a_jadwal_kerja_kelompok a 
			WHERE a.jadwal_kerja_kelompok_id=".$jadwal_kerja_kelompok_id." 
			ORDER BY a.jadwal_kerja_kelompok_id ";
		$jadwal_kerjas = my_fetch_array(my_query($query));	
		$waktu_kerja_id=$jadwal_kerjas['waktu_kerja_id'] ;
		$kelompok=$jadwal_kerjas['kelompok'] ;
		if($waktu_kerja_id > 3){  //shift
			if($kelompok=='X'){
				$status_absen_id=2 ; //X
			}else{
				$status_absen_id=(int) $kelompok+2 ; //1,2,3
			}
		}else{ //non shift
			if(get_hari_libur($date)){	
				$status_absen_id=2 ; //X
			}else{
				$status_absen_id=1 ; //N
			}	
		}
		return $status_absen_id;	
	}	
	return 0;
}

function get_hari_id_by_date($date){
	$hari_id=date("N",strtotime($date) ); //Monday to Sunday
	//konversi ke Sunday to Saturday
	if($hari_id==7){
		$hari_id=1;
	}else{
		$hari_id++;
	}		
	return $hari_id;
}

function get_hari_libur($date){
	if(is_sabtu_minggu($date)){
		return true;
	}
	$query="SELECT hari_libur_id FROM a_hari_libur
		WHERE tanggal = '{$date}'";
	if (my_num_rows( my_query($query)) >0){	
		return true;
	}	
	return false;
}

function get_kelompok_shift_jadwal($tanggal , $kelompok_shift_id ){
	$periode_id=get_periode_absen();
	list($yyyy,$mm,$dd) = explode("-" , $tanggal);
	$query = "SELECT tgl_{$dd} FROM a_kalendar_shift 
		WHERE periode_id = {$periode_id} 
		AND kelompok_shift_id={$kelompok_shift_id}";
	$result = my_query($query);
	$re = my_fetch_array($result);
	return $re['tgl_'.$dd];
}

//===========================================================

function update_periode_id(){
	$query = "
		SELECT periode_id,tanggal,tahun FROM pr_periode 
		WHERE absen = 'Active'";
	$res = my_query($query);
	$rw = my_fetch_array($res);
	$periode_id=$rw['periode_id'];
	$bulan = (int) date('m' , strtotime($rw['tanggal']));
	$tahun = $rw['tahun'] ;
	//Reset Active->Void
	$datas = array(
		'absen'=>my_type_data_str('Void'),	
	);
	my_update_record('pr_periode','periode_id',$periode_id,$datas);		
	//cek apakah periode id berikutnya sudah ada
	$next_periode=get_periode_berikut($periode_id);
	if($next_periode==0){
		$tgl_dari=$tahun.'-'.sprintf( '%02d',$bulan) .'-11';
		if($bulan ==12 ){
			$tahun++ ;
			$tanggal = $tahun.'-01-01';
			$tgl_sampai=$tahun.'-01-10';
		}else{
			$tanggal = $tahun.'-'.sprintf( '%02d',$bulan+1) .'-01';
			$tgl_sampai=$tahun.'-'.sprintf( '%02d',$bulan+1) .'-10';
		}

		$datas = array(
			'tahun'=>my_type_data_str($tahun),
			'tanggal'=>my_type_data_str($tanggal),
			'absen'=>my_type_data_str('Active'),
			'gaji'=>my_type_data_str('Void'),
			'dari'=>my_type_data_str($tgl_dari),
			'sampai'=>my_type_data_str($tgl_sampai),
		);
		$periode_id = my_insert_record('pr_periode' , $datas);
	}else{
		$periode_id=$next_periode;
		$datas = array(
			'absen'=>my_type_data_str('Active'),	
		);
		my_update_record('pr_periode','periode_id',$next_periode,$datas);		
	}
	return $periode_id;
}

function update_a_absen_payroll(){
	$periode_id =get_periode_absen();
	$last_periode_id=get_last_periode_id($periode_id) ;
	$periodes = my_get_data_by_id('pr_periode','periode_id',$periode_id);
	$bulan = (int) date('m' , strtotime($periodes['tanggal']));
	$tahun = $periodes['tahun'] ;
	$end=$tahun.'-'. sprintf( '%02d',$bulan) .'-10' ;
	$enddate_11_10=$tahun.'-'.sprintf( '%02d',$bulan) .'-10';
	if($bulan =='01' ){
		$bulan = '12';
		$tahun-- ;	
	}else{
		$bulan-- ;
	}		
	$startdate_11_10=$tahun.'-'.sprintf( '%02d',$bulan) .'-11';	
	$startdate_01_31=$tahun.'-'.sprintf( '%02d',$bulan) .'-01';	
	$tgl_akhir=date('d', strtotime(get_last_date_by_month($tahun,$bulan))) ;
	$enddate_01_31=$tahun.'-'.sprintf( '%02d',$bulan) .'-'.$tgl_akhir;
	
	$query = "DELETE FROM a_absen_payroll WHERE periode_id ={$periode_id} ";
	my_query($query);

	$query = "
		SELECT a.karyawan_id, b.kelompok_shift_id  FROM karyawan a 
		INNER JOIN kekaryawanan b ON a.karyawan_id=b.karyawan_id 
		WHERE a.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable)
		AND a.karyawan_id NOT IN (select karyawan_id FROM pemberhentian  WHERE TMT <= '{$end}') 	

	";
	$result = my_query($query);
	while($ey = my_fetch_array($result)){
		$karyawan_id=$ey['karyawan_id'];
		$kelompok_shift_id= isset($ey['kelompok_shift_id']) ? $ey['kelompok_shift_id']: 0;
		
		//HITUNG LEMBUR 
		$query = "SELECT * FROM a_spl_det 
				WHERE karyawan_id=".$ey['karyawan_id']."  
				AND periode_id={$periode_id} 
				" ;	
		$res = my_query($query);
		$lembur_jam=$lembur_lebih4jam=0;
		$lembur_jumlah_jam=$hourpay=$hourpay_56=$nact=$nact1= 0;
		while($ey1 = my_fetch_array($res)){
			if($ey1['jenis']=="Koreksi" or $ey1['jenis']=="Hapus")	{	
				$query = "SELECT * FROM a_spl_det	
					WHERE spl_id={$ey1['spl_id']}  AND spl_det_id<{$ey1['spl_det_id']} 
					ORDER BY spl_det_id DESC
					LIMIT 1" ;
				$spl_koreksis = my_fetch_array(my_query($query)) ;	
				$hourpay-=$spl_koreksis['hourpay'] ;
			}	
			if($ey1['jenis']=="Koreksi" or $ey1['jenis']=="Tambah")	{	
				$hourpay+=$ey1['hourpay'] ;
			}	
// + cek lagi
			if($ey1['tanggal']<=$enddate_01_31){
				//tambahan u makan
				$lembur_jam=$ey1['jumlah_jam'] ;
				if( $lembur_jam  >= 16){
					$lembur_lebih4jam+=3 ;
				}elseif( $lembur_jam  >= 12){	
					$lembur_lebih4jam+=2 ;
				}elseif( $lembur_jam  >=4){	
					$lembur_lebih4jam+=1 ;
				}else{
				}
			}
			if($ey1['tanggal']>=$startdate_11_10){
				$lembur_jumlah_jam+=$ey1['jumlah_jam'];
				$hourpay+=$ey1['hourpay'];
				if($nact+$ey1['jumlah_jam']<=56){
					$nact+=$ey1['jumlah_jam'];
					$hourpay_56+=$ey1['hourpay'];
				}else{	
					$nact1+=$ey1['jumlah_jam'];
				}			
			}


// end of cek lagi
		}
		if($nact1<>0 AND $nact <=56){
			if(($nact1+$nact) > 56){
				$hourpay_56+=((56-$nact)* 2 - 0.5 );
			}else{	
				$hourpay_56+=($nact1* 2 - 0.5 );
			}
		}
		//HITUNG KEHADIRAN 	
		$potong_absen_jam=$potong_transport=0;
		$query = "SELECT * FROM a_realisasi_log 
				WHERE karyawan_id={$karyawan_id}  
				AND	periode_id={$periode_id}
			 ";	
		$res1 = my_query($query); 	
		$hari_kerja_11_10=$lembur_otomatis=0;
		$hari_kerja_1_31=0;
		$cito_11_10=$insentif_pot_hari_1_31=$insentif_pot_jam_1_31=$insentif_pot_menit_1_31=0;
		while($ey1 = my_fetch_array($res1)){
			$status_absens=my_get_data_by_id('a_status_absen','status_absen_id',$ey1['status_absen_id']) ;
			if((int) $ey1['status_absen_id']==16 ){  //nanti direvisi status absen + kolom potong absensi
				$potong_absen_jam+=8;
			}
			if($status_absens['potong_transport']=="Y"){
				$potong_transport++;
			}

			if($status_absens['hari_kerja']=="Y"){
				$hari_kerja_11_10++;
			}
			if($status_absens['lembur_otomatis']=="Y"){
				$lembur_otomatis++;
			}			
			//HITUNG KEHADIRAN 11-... (=BULAN SEBELUMNYA)UNTUK INSENTIF	
			if( (int) str_replace('tgl_','',$ey1['field_tgl'])  > 10){
				if($status_absens['potong_insentif']=="Y"){
					$insentif_pot_hari_1_31++;
				}		
				if($status_absens['hari_kerja']=="Y"){
					$hari_kerja_1_31++;
				}
			}
		}

		// HITUNG ABSEN
		$query = "
			SELECT * FROM a_surat_ijin	WHERE 
			tanggal BETWEEN '".$startdate_11_10."' AND '".$enddate_11_10."' 
			AND karyawan_id=".$karyawan_id." 
			AND rekomendasi='T'"
		;
		$res = my_query($query);
		while($ey = my_fetch_array($res)){
			if( $ey['full_day']=='Y'){
			}else{
				if($ey['jam_sampai']>$ey['jam_dari']){
					$jam_dari=$ey['jam_dari'];
					$jam_sampai=$ey['jam_sampai'];
				}else{
					$jam_dari=$ey['jam_sampai'];
					$jam_sampai=$ey['jam_dari'];
				}
				$jam_menit=strtotime('2001-01-01 '.time_min($jam_sampai,$jam_dari));
				$potong_absen_jam+=(int) date("H",$jam_menit );
				$potong_absen_jam+=(int) date("i",$jam_menit )/60;
			}
		}
		
		//HITUNG KEHADIRAN 1-10 UNTUK INSENTIF
		$query = "SELECT * FROM a_realisasi_log 
				WHERE karyawan_id=".$karyawan_id." 
				AND	periode_id=".($last_periode_id)." 
				AND field_tgl IN (
				'tgl_01', 
				'tgl_02', 
				'tgl_03', 
				'tgl_04', 
				'tgl_05', 
				'tgl_06', 
				'tgl_07', 
				'tgl_08', 
				'tgl_09', 
				'tgl_10' )"
			 ;	
		$res2 = my_query($query); 	
		while($ey2 = my_fetch_array($res2)){
			$status_absens=my_get_data_by_id('a_status_absen','status_absen_id',$ey2['status_absen_id']) ;
			if($status_absens['potong_insentif']=="Y"){
				$insentif_pot_hari_1_31++;
			}
			if($status_absens['hari_kerja']=="Y"){
				$hari_kerja_1_31++;
			}
		}	
		// HITUNG POTONGAN INSENTIF JAM & MENIT
		$query = "
			SELECT * FROM a_surat_ijin	WHERE MONTH(tanggal)='".$bulan."'
			AND YEAR(tanggal)='".$tahun."' AND karyawan_id=".$karyawan_id." 
			AND potong_insentif='Y'"
		;

		$res = my_query($query);
		while($ey = my_fetch_array($res)){
			if( $ey['full_day']=='Y'){
			}else{
				if($ey['jam_sampai']>$ey['jam_dari']){
					$jam_dari=$ey['jam_dari'];
					$jam_sampai=$ey['jam_sampai'];
				}else{
					$jam_dari=$ey['jam_sampai'];
					$jam_sampai=$ey['jam_dari'];
				}
				$jam_menit=strtotime('2001-01-01 '.time_min($jam_sampai,$jam_dari));
				$insentif_pot_jam_1_31+=(int) date("H",$jam_menit );
				$insentif_pot_menit_1_31+=(int) date("i",$jam_menit );
			}
		}
	
		// Hitung Work On Call
		$query = "SELECT dua_jam,empat_jam from woc_tarif a 
			INNER JOIN kekaryawanan b ON a.golongan_jabatan_id=b.golongan_jabatan_id 
			WHERE b.karyawan_id = {$karyawan_id }";	
		$tarifs = my_fetch_array(my_query($query));

		$query = "SELECT tanggal,jumlah_jam FROM work_on_call_detail a 
			INNER JOIN work_on_call b ON a.work_on_call_id=b.work_on_call_id 	
			WHERE a.karyawan_id={$karyawan_id} 
			AND b.tanggal BETWEEN '".$startdate_11_10."' AND '".$enddate_11_10."' 
			ORDER BY b.tanggal " ;
		$res = my_query($query);

		$tgl=false;
		$cito_jam=$cito_lebih4jam=0;
		while($ey = my_fetch_array($res)){

			if( (int) $ey['jumlah_jam']  < 4){
				$cito_11_10+=$ey['jumlah_jam']*$tarifs['dua_jam'] ;
			}else{
				$cito_11_10+=$ey['jumlah_jam']*$tarifs['empat_jam'] ;
			}
			//tambahan u makan
			if($ey['tanggal']==$tgl){
			}else{
				if( $cito_jam  >= 4){
					$cito_lebih4jam+=1 ;
				}			
				$tgl=$ey['tanggal'] ;
				$cito_jam=0;
			}
			$cito_jam+=$ey['jumlah_jam'];
		}
		if($tgl){ //data terakhir
				if( $cito_jam  >= 4){
					$cito_lebih4jam+=1 ;
				}			
		}		
		
		//UPDATE a_absen_payroll
		$hourpay_56=round($hourpay_56);
		$datas = array(
			'karyawan_id'=>my_type_data_int($karyawan_id), 
			'periode_id'=>my_type_data_int($periode_id), 
			'hari_kerja_11_10'=>my_type_data_int($hari_kerja_11_10), 
			'lembur_otomatis'=>my_type_data_int($lembur_otomatis), 
			'hourpay'=>my_type_data_str($hourpay), 
			'hourpay_56'=>my_type_data_str($hourpay_56), 
			'lembur_jumlah_jam'=>my_type_data_str($lembur_jumlah_jam), 
			'lembur_lebih4jam'=>my_type_data_int($lembur_lebih4jam), 
			'hari_kerja_1_31'=>my_type_data_int($hari_kerja_1_31), 
			'cito_11_10'=>my_type_data_int($cito_11_10), 
			'cito_lebih4jam'=>my_type_data_int($cito_lebih4jam), 
			'insentif_pot_hari_1_31'=>my_type_data_int($insentif_pot_hari_1_31), 
			'insentif_pot_jam_1_31'=>my_type_data_int($insentif_pot_jam_1_31), 
			'insentif_pot_menit_1_31'=>my_type_data_int($insentif_pot_menit_1_31), 
			'potong_absen_jam'=>my_type_data_str($potong_absen_jam), 
			'potong_transport'=>my_type_data_int($potong_transport), 
			'kelompok_shift_id'=>my_type_data_int($kelompok_shift_id), 
		);
		my_insert_record('a_absen_payroll' , $datas);						
	}	
   return true;
} 

function kalendar_shift_proses_update($periode_id  ){
		$query = "SELECT * FROM a_kelompok_shift a
			INNER JOIN a_waktu_kerja b ON a.waktu_kerja_id = b.waktu_kerja_id
			WHERE b.jenis='Shift' ";
		$result = my_query($query);
		while($row =my_fetch_array($result)){
			 $query_check_kalender = "SELECT * FROM a_kalendar_shift 
				WHERE periode_id={$periode_id} AND kelompok_shift_id ={$row['kelompok_shift_id']} ";
			$result_check_kalendar = my_query($query_check_kalender );
			if(my_num_rows($result_check_kalendar) == 0){
				$datas_kalendar = array(
					'periode_id'=>my_type_data_int($periode_id),
					'kelompok_shift_id'=>my_type_data_int($row['kelompok_shift_id']),
				);
				$kalendar_shift_id = my_insert_record('a_kalendar_shift',$datas_kalendar);
						
			}
			set_nilai_by_date($periode_id,$row['kelompok_shift_id'],$row['pola_id']);	
		 }
}

function set_nilai_by_date($periode_id,$kelompok_shift_id,$pola_id){
	$periode = my_get_data_by_id('pr_periode','periode_id',$periode_id);
	$month = date('m',strtotime($periode['tanggal']));
	$year = date('Y',strtotime($periode['tanggal']));
	if($month==1){
		$month=12;
		$year--;
	}else{
		$month--;
	}
	$tanggal=$year.'-'.sprintf( '%02d',$month) .'-01';
	$periods = my_get_data_by_id('pr_periode','tanggal',"'".$tanggal."'");
	$last_periode_id=$periods['periode_id'];
	$last_date=(int) date('d', strtotime(get_last_date_by_month($year,$month))) ;
	if($pola_id>0){
		$last_shift='';
		for($i = 1; $i<=10; $i++ ){
			$last_shift=$last_shift.get_shift_by_ndate($i ,$last_periode_id,$kelompok_shift_id);
		} 	
		$polas=my_get_data_by_id('a_pola_shift','pola_shift_id',$pola_id);
		$konstanta_harian =str_replace('-','',$polas['pola']) ;
		$banyak_pola=strlen($konstanta_harian) ;
		$temp=$konstanta_harian.$konstanta_harian ;
		while(strlen($temp)<19){
			$temp.=$konstanta_harian ;
		}	
		if($last_shift==''){
			//var_dump($kelompok_shift_id);
			//var_dump($last_shift);
			//var_dump($pos);
			//exit;
			$pos=false;
		}else{	
			$pos=strpos($temp,$last_shift) ;
		}	
		unset($temp);
		$konstanta_harian = explode("-" ,$polas['pola'] );

		$pos+=10 ; //karakter selanjutnya dari pola
		$pos=$pos%$banyak_pola ;
		
		$t=10;
		for($i = 1 ; $i<=$last_date ;$i++ ){
			$t++;
			if($t>$last_date){
				$t=1;
			}	
			$tgl=sprintf('%02d',$t);
			$shift_data_dd = $konstanta_harian[$pos];
			$pos++;
			if($pos>$banyak_pola-1){
				$pos=0;
			}	

			$query = "UPDATE a_kalendar_shift SET tgl_{$tgl} ='{$shift_data_dd}' WHERE periode_id={$periode_id} AND kelompok_shift_id ={$kelompok_shift_id}  ";
			my_query($query);
		}	
	}else{
		// shift lain - ikuti bulan sebelumnya
		for($i = 1; $i<=31; $i++ ){
			$last_shift=get_shift_by_ndate($i ,$last_periode_id,$kelompok_shift_id);
			$tgl=sprintf('%02d',$i);
			$query = "UPDATE a_kalendar_shift SET tgl_{$tgl} ='{$last_shift}' WHERE periode_id={$periode_id} AND kelompok_shift_id ={$kelompok_shift_id}  ";
				my_query($query);
		} 		
	}	
}	

function get_shift_by_ndate($dd , $periode_id,$kelompok_shift_id){
	if($dd == 0) return NULL;
	$dd = sprintf('%02d',$dd); 
	$query = "SELECT tgl_{$dd} FROM a_kalendar_shift WHERE periode_id={$periode_id} AND kelompok_shift_id={$kelompok_shift_id}";
	$result = my_query($query);
	$row = my_fetch_array($result );
	$datevalue = isset($row['tgl_'.$dd]) ?$row['tgl_'.$dd]: '';
	if($datevalue <> ''){
		return $datevalue;
	}
	return NULL;
}

function form_pilih_periode(){
	$periode_id =get_periode_absen();

	$view = form_header( "..." , "..."  );
	 
	$query_check_periode = " SELECT * FROM pr_periode ORDER BY periode_id DESC LIMIT 10";
	$result = my_query($query_check_periode);
	$prs = array();
	while($prow = my_fetch_array($result)){
		$prs[$prow['periode_id']] = date('Y-m',strtotime($prow['tanggal']));
	}
	$periodes = array(
		'id'=>'periode_id',
		'value'=>$periode_id ,
		'name'=>'periode_id',
	);
	//$form_bulan
	$view .= form_field_display( form_dropdown($periodes,$prs)   , "Periode Absen"   );
	//$view .= form_field_display( '<input type="checkbox" id="schedule" name="csd" value="1"> Create schedule'  , "&nbsp;"   );
	  	
	$excel = array(
		'value' => (  ' Tutup Bulan '  ),
		'onclick' => 'javascript:jQuery.facebox({ ajax: \'index.php?com='.$_GET['com'].'&schedule=\'+ $(\'#schedule\').is(\':checked\')+\'&task=kga&periode_id=\'+ $(\'#periode_id\').val()+\'&\' });', 
		'type'=>'button','class'=>'main_button'
	);
	$form_excel= form_dynamic($excel); 
	
	$view .= form_field_display( $form_excel  , "&nbsp;" ,  "" );
	$view .= form_footer( );
	return $view;
}
function page_kalkulasi2(){
	my_set_code_js('
	$(document).ready(function() {
       $(\'#onebar\').load(\'index.php?com='.$_GET['com'].'&task=tutup_bulan_all&periode_id='.$_GET['periode_id'].'&schedule='.$_GET['schedule'].'&p='.rand(0,999).'\'); 
	 
    });
	');
	return '<div id="onebar" style="width:100%;text-align:center;"><img src="components/system/jquery/facebox/loading.gif" /><br/>On processing!!!</div>';
}

//====================================
function get_startdate_enddate(){
	$res=array();
	$periode_id =get_periode_absen();
	$periode = my_get_data_by_id('pr_periode','periode_id',$periode_id);
	$bulan = (int) date('m' , strtotime($periode['tanggal']));
	$tahun = $periode['tahun'] ;
	$res['enddate']=$tahun.'-'.sprintf( '%02d',$bulan) .'-10';
	if($bulan =='01' ){
		$bulan = '12';
		$tahun-- ;	
	}else{
		$bulan-- ;
	}		
	$res['startdate']=$tahun.'-'.sprintf( '%02d',$bulan) .'-11';
	return $res ;
}	

function formula_get_hari_bulan(){
	$periode_id =get_periode_gaji();
	$periode = my_get_data_by_id('pr_periode','periode_id',$periode_id);
	$bulan = (int) date('m' , strtotime($periode['tanggal']));
	$tahun = $periode['tahun'] ;
	if($bulan =='12' ){
		$bulan = '01';
		$tahun ++ ;	
	}else{
		$bulan++ ;
	}		
	$startdate=$tahun.'-'.sprintf( '%02d',$bulan) .'-01';
	$tgl_akhir=date('d', strtotime(get_last_date_by_month($tahun,$bulan))) ;

	$enddate=$tahun.'-'.sprintf( '%02d',$bulan) .'-'.$tgl_akhir;
	$res=jumlah_hari_not_weekend($startdate , $enddate  );
	return $res;
}	

function get_periode_berikut($periode_id){
	$periode = my_get_data_by_id('pr_periode','periode_id',(int) $periode_id);
	$month = date('m',strtotime($periode['tanggal']));
	$year = date('Y',strtotime($periode['tanggal']));
	if($month==12){
		$month=1;
		$year++;
	}else{
		$month++;
	}
	$tanggal=$year.'-'.sprintf( '%02d',$month) .'-01';
	$periods = my_get_data_by_id('pr_periode','tanggal',"'".$tanggal."'");
	$res=(int) $periods['periode_id'];
	return $res;
}


function compress_sql_waktu_kerja($periode_id){
	$fsql = $_SERVER['DOCUMENT_ROOT']."/files/compressing_backup/sebelum_end_wk_".DATABASE_NAME."_".date("Ymd_His").".sql";
	//$ftar = $_SERVER['DOCUMENT_ROOT']."/files/compressing_backup/database_".DATABASE_NAME."_".date("Ymd_His").".sql.tar";
	$link = "files/compressing_backup/waktu_kerja_".DATABASE_NAME."_".date("Ymd_His").".sql.tar";
	$query = "SHOW TABLES";
	$hasil = my_query($query);
	$tbls='';
	while($r = my_fetch_row($hasil)){
		$tbls .= $r[0].' ';
	}
	
	$mysqldump_command = "mysqldump -h ".DATABASE_HOST." -u ".DATABASE_USER." -p".DATABASE_PASSWORD." ".DATABASE_NAME."   > {$fsql}";
 
	exec($mysqldump_command);
	if(file_exists($fsql)){
		 
		return true;
	}
	return false;
}